-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE NTDBNEW
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Sushil Mittal
-- Create date: 
-- Description:	
-- =============================================
IF OBJECT_ID('sp_NTDBNEW_Imputation_ExtractRelevantColsFromAllTables14_ToCDS14_for_CDS') IS NOT NULL
    DROP PROCEDURE sp_NTDBNEW_Imputation_ExtractRelevantColsFromAllTables14_ToCDS14_for_CDS

GO
CREATE PROCEDURE sp_NTDBNEW_Imputation_ExtractRelevantColsFromAllTables14_ToCDS14_for_CDS
	-- Add the parameters for the stored procedure here
	--@TableName NVARCHAR(100)
AS
BEGIN
	-- Insert statements for procedure here	SET NOCOUNT ON;

IF OBJECT_ID('CDS14') IS NOT NULL
    DROP TABLE CDS14

SELECT DISTINCT

/*DEMO_INC_KEY */ INC_KEY,
/*AISCCODE     */ AISPCODE_PREDOT,
/*DEMO         */ DEMO_AGE,
/*DISCHARGE    */ DISCHARGE_HOSPDISP,
/*ECODE        */ SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) AS ECODE1, SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) AS ECODE2,
/*VITALS       */ VITALS_GCSEYE, VITALS_GCSVERB, VITALS_GCSMOT, VITALS_GCSTOT,
/*ED           */ ED_EDDISP, ED_YOINJ

INTO CDS14

FROM

AllTables14

WHERE

AISCCODE_SEVERITY != 9

AND VITALS_VSTYPE != 'EMS'

AND

DISCHARGE_HOSPDISP != 'Discharged/Transferred to a short-term general hospital for inpatient' AND
DISCHARGE_HOSPDISP != 'Discharged/Transferred to another acute care hospital using EMS' AND
DISCHARGE_HOSPDISP != 'Not Applicable BIU 1' AND
DISCHARGE_HOSPDISP != 'Not Known BIU 2' AND
DISCHARGE_HOSPDISP != 'Not Known/Not Recorded BIU 2' AND
DISCHARGE_HOSPDISP != 'Not Recorded BIU 3' AND
ED_EDDISP != 'Transferred to another hospital'

AND

(SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8100' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8101' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8104' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8110' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8111' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8114' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8120' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8121' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8124' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8130' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8131' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8134' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8140' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8141' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8144' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8150' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8151' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8154' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8160' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8161' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8164' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8170' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8171' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8174' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8180' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8181' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8184' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8190' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8191' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE,'.','') + '0',1,5) = 'E8194' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8100' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8101' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8104' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8110' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8111' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8114' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8120' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8121' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8124' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8130' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8131' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8134' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8140' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8141' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8144' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8150' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8151' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8154' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8160' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8161' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8164' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8170' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8171' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8174' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8180' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8181' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8184' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8190' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8191' OR
SUBSTRING('E' + REPLACE(ECODE_ECODE2,'.','') + '0',1,5) = 'E8194')

END
GO
